# 作者:小宇
# 2025年11月12日13时23分04秒
# 1225074067@qq.com
import pandas as pd
import json
import re
from pathlib import Path


def extract_climate_data_from_text(text):
    """从文本中提取气候相关信息"""
    if pd.isna(text) or not isinstance(text, str):
        return {}

    result = {}

    # 提取年份
    year_patterns = [
        r'(\d{3,4})\s*年',
        r'公元\s*(\d{3,4})',
        r'至元.*?(\d+)\s*年',
        r'大定.*?(\d+)\s*年',
        r'贞元.*?(\d+)\s*年',
        r'泰定.*?(\d+)\s*年',
        r'光绪.*?(\d+)\s*年',
        r'(\d{4})年',
    ]

    # 提取降水量相关数值
    precipitation_patterns = [
        r'雨量.*?(\d+(?:\.\d+)?)\s*毫米',
        r'降水量.*?(\d+(?:\.\d+)?)\s*毫米',
        r'(\d+(?:\.\d+)?)\s*毫米.*?雨',
        r'平均降水量.*?(\d+(?:\.\d+)?)\s*[～\-]\s*(\d+(?:\.\d+)?)\s*毫米',
    ]

    # 提取温度相关数值
    temperature_patterns = [
        r'(\-?\d+(?:\.\d+)?)\s*℃',
        r'气温.*?(\-?\d+(?:\.\d+)?)\s*[～\-]\s*(\-?\d+(?:\.\d+)?)\s*度',
        r'年均温.*?(\d+(?:\.\d+)?)\s*℃',
        r'平均气温.*?(\-?\d+(?:\.\d+)?)\s*℃',
    ]

    # 提取风速
    wind_speed_patterns = [
        r'风速.*?(\d+(?:\.\d+)?)\s*米/秒',
        r'平均风速.*?(\d+(?:\.\d+)?)\s*米/秒',
        r'(\d+(?:\.\d+)?)\s*米/秒.*?风速',
    ]

    # 提取湿度
    humidity_patterns = [
        r'湿度.*?(\d+(?:\.\d+)?)\s*%',
        r'相对湿度.*?(\d+(?:\.\d+)?)\s*%',
        r'(\d+(?:\.\d+)?)\s*%.*?湿度',
    ]

    # 提取年份
    for pattern in year_patterns:
        match = re.search(pattern, text)
        if match:
            try:
                year = int(match.group(1))
                if 1000 <= year <= 2100:  # 合理的年份范围
                    result['year'] = year
                    break
            except:
                pass

    # 提取降水量
    for pattern in precipitation_patterns:
        match = re.search(pattern, text)
        if match:
            try:
                if len(match.groups()) == 2:  # 范围值
                    min_val = float(match.group(1))
                    max_val = float(match.group(2))
                    result['precipitation'] = {
                        'min': min_val,
                        'max': max_val,
                        'unit': 'mm'
                    }
                else:  # 单个值
                    result['precipitation'] = {
                        'value': float(match.group(1)),
                        'unit': 'mm'
                    }
                break
            except:
                pass

    # 提取温度
    for pattern in temperature_patterns:
        match = re.search(pattern, text)
        if match:
            try:
                if len(match.groups()) == 2:  # 范围值
                    min_val = float(match.group(1))
                    max_val = float(match.group(2))
                    result['temperature'] = {
                        'min': min_val,
                        'max': max_val,
                        'unit': '℃'
                    }
                else:  # 单个值
                    result['temperature'] = {
                        'value': float(match.group(1)),
                        'unit': '℃'
                    }
                break
            except:
                pass

    # 提取风速
    for pattern in wind_speed_patterns:
        match = re.search(pattern, text)
        if match:
            try:
                result['wind_speed'] = {
                    'value': float(match.group(1)),
                    'unit': 'm/s'
                }
                break
            except:
                pass

    # 提取湿度
    for pattern in humidity_patterns:
        match = re.search(pattern, text)
        if match:
            try:
                result['humidity'] = {
                    'value': float(match.group(1)),
                    'unit': '%'
                }
                break
            except:
                pass

    # 判断天气类型
    weather_types = []
    if any(word in text for word in ['大雪', '降雪', '雪花', '雪满']):
        weather_types.append('snow')
    if any(word in text for word in ['暴雨', '大雨', '强降雨', '雨量']):
        weather_types.append('heavy_rain')
    if any(word in text for word in ['沙尘暴', '飞砂', '扬砾', '天雨红沙']):
        weather_types.append('sandstorm')
    if any(word in text for word in ['大风', '狂风', '风速']):
        weather_types.append('strong_wind')
    if any(word in text for word in ['酷暑', '炎热']):
        weather_types.append('heat')
    if any(word in text for word in ['严寒', '寒冷', '苦寒']):
        weather_types.append('cold')

    if weather_types:
        result['weather_types'] = weather_types

    return result


def extract_season_from_text(text):
    """从文本中提取季节信息"""
    if pd.isna(text) or not isinstance(text, str):
        return None

    season_mapping = {
        '春': 'spring',
        '夏': 'summer',
        '秋': 'autumn',
        '冬': 'winter',
        '正月': 'january',
        '二月': 'february',
        '三月': 'march',
        '四月': 'april',
        '五月': 'may',
        '六月': 'june',
        '七月': 'july',
        '八月': 'august',
        '九月': 'september',
        '十月': 'october',
        '十一月': 'november',
        '十二月': 'december',
        '元旦': 'new_year',
        '重阳': 'chongyang',
    }

    for chinese, english in season_mapping.items():
        if chinese in text:
            return english

    return None


def extract_data_from_climate_excel(file_path, sheet_name='总气候'):
    """从气候Excel文件中提取数据"""
    try:
        # 读取Excel文件
        df = pd.read_excel(file_path, sheet_name=sheet_name)

        results = []

        for idx, row in df.iterrows():
            # 跳过完全空白的行
            if row.isnull().all():
                continue

            # 获取时期（朝代）
            category = row.iloc[0] if not pd.isna(row.iloc[0]) else 'unknown'

            # 获取原文内容
            content = row.iloc[1] if len(row) > 1 and not pd.isna(row.iloc[1]) else ''

            # 获取其他列的数据
            source = row.iloc[2] if len(row) > 2 and not pd.isna(row.iloc[2]) else ''
            precipitation_desc = row.iloc[3] if len(row) > 3 and not pd.isna(row.iloc[3]) else ''
            season_desc = row.iloc[4] if len(row) > 4 and not pd.isna(row.iloc[4]) else ''
            temperature_desc = row.iloc[5] if len(row) > 5 and not pd.isna(row.iloc[5]) else ''
            extreme_weather_desc = row.iloc[6] if len(row) > 6 and not pd.isna(row.iloc[6]) else ''

            # 从原文中提取气候数据
            extracted_data = extract_climate_data_from_text(str(content))

            # 提取季节信息
            season = extract_season_from_text(str(season_desc) if season_desc else str(content))

            # 创建数据记录
            record = {
                'category': str(category).strip(),
                'description': str(content),
                'source': str(source),
                'precipitation_description': str(precipitation_desc),
                'season_description': str(season_desc),
                'temperature_description': str(temperature_desc),
                'extreme_weather_description': str(extreme_weather_desc),
            }

            # 添加提取的数据
            if 'year' in extracted_data:
                record['year'] = extracted_data['year']
            if 'precipitation' in extracted_data:
                record['precipitation_data'] = extracted_data['precipitation']
            if 'temperature' in extracted_data:
                record['temperature_data'] = extracted_data['temperature']
            if 'wind_speed' in extracted_data:
                record['wind_speed'] = extracted_data['wind_speed']
            if 'humidity' in extracted_data:
                record['humidity'] = extracted_data['humidity']
            if 'weather_types' in extracted_data:
                record['weather_types'] = extracted_data['weather_types']
            if season:
                record['season'] = season

            # 只添加有内容的记录
            if any([record['description'], record['precipitation_description'],
                    record['temperature_description'], record['extreme_weather_description']]):
                results.append(record)

        return results

    except Exception as e:
        print(f"读取文件时出错: {e}")
        return []


def process_all_sheets(file_path):
    """处理Excel文件中的所有工作表"""
    try:
        # 获取所有工作表名称
        excel_file = pd.ExcelFile(file_path)
        sheet_names = excel_file.sheet_names

        all_data = {}

        for sheet_name in sheet_names:
            print(f"正在处理工作表: {sheet_name}")
            data = extract_data_from_climate_excel(file_path, sheet_name)
            all_data[sheet_name] = data
            print(f"  - 从 '{sheet_name}' 提取了 {len(data)} 条记录")

        return all_data

    except Exception as e:
        print(f"处理工作表时出错: {e}")
        return {}


def main():
    # 输入文件路径
    input_file = './data/03气候 - 总数据和各朝代数据.xlsx'

    # 输出文件路径
    output_file = './outputs/climate_data.json'

    print("开始提取气候数据...")

    # 处理所有工作表
    all_data = process_all_sheets(input_file)

    # 保存为JSON文件
    Path(output_file).parent.mkdir(parents=True, exist_ok=True)
    with open(output_file, 'w', encoding='utf-8') as f:
        json.dump(all_data, f, ensure_ascii=False, indent=2)

    print(f"\n数据已保存到: {output_file}")

    # 显示统计信息
    total_records = sum(len(data) for data in all_data.values())
    print(f"\n总共提取了 {total_records} 条有效记录")

    # 显示各工作表的记录数
    print("\n各工作表记录统计:")
    for sheet_name, data in all_data.items():
        print(f"  {sheet_name}: {len(data)} 条记录")

    # 显示前几条数据作为示例
    print("\n前3条数据示例 (总气候工作表):")
    if '总气候' in all_data and all_data['总气候']:
        for i, record in enumerate(all_data['总气候'][:3], 1):
            print(f"\n记录 {i}:")
            print(f"  时期(category): {record['category']}")
            if 'year' in record:
                print(f"  年份(year): {record['year']}")
            if 'season' in record:
                print(f"  季节(season): {record['season']}")
            if 'weather_types' in record:
                print(f"  天气类型(weather_types): {record['weather_types']}")
            if 'precipitation_data' in record:
                print(f"  降水量(precipitation_data): {record['precipitation_data']}")
            if 'temperature_data' in record:
                print(f"  温度(temperature_data): {record['temperature_data']}")

    return all_data


if __name__ == '__main__':
    main()